﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ReklamaWPF
{
    public class DataBase
    {
        private string server = null;
        private string user = null;
        private string pass = null;
        private string database = null;
        private string dbcon = null;
        private SqlConnection m_cnADONetConnection;
        public SqlDataAdapter Ad_ms_Table;
        private SqlCommandBuilder CmBl_ms_Table;
        DataTable Tb_Table;

        public DataBase(string server, string user, string pass, string database)
        {
            this.server = server;
            this.user = user;
            this.pass = pass;
            this.database = database;
            dbcon = "server=" + this.server + ";uid=" + this.user + ";pwd=" + this.pass + ";database=" + this.database;
        }

        public DataBase(string server, string database)
        {
            this.server = server;
            this.database = database;
            dbcon = "server=" + this.server + ";database=" + this.database + ";Trusted_Connection=yes";
        }

        public bool checkConnection()
        {
            bool chk = true;
            try
            {
                m_cnADONetConnection = new SqlConnection(dbcon);
                m_cnADONetConnection.Open();
                m_cnADONetConnection.Close();
            }
            catch (SqlException ex) { chk = false; }
            return chk;
        }

        public DataTable getTable(string sql)
        {
            Tb_Table = new DataTable();
            m_cnADONetConnection = new SqlConnection(dbcon);
            Ad_ms_Table = new SqlDataAdapter();
            CmBl_ms_Table = new SqlCommandBuilder(Ad_ms_Table);
            Tb_Table = new DataTable();
            Ad_ms_Table.SelectCommand = new SqlCommand(sql, m_cnADONetConnection);
            m_cnADONetConnection.Open();
            Tb_Table.Clear();
            Ad_ms_Table.Fill(Tb_Table);
            m_cnADONetConnection.Close();
            return Tb_Table;
        }

        public void currentDsUpdate()
        {
            Ad_ms_Table.Update(Tb_Table);
        }

        public bool sqlExecute(string sql)
        {
            bool commited = false;
            m_cnADONetConnection = new SqlConnection(dbcon);
            SqlCommand cmd = new SqlCommand(sql, m_cnADONetConnection);
            try
            {
                m_cnADONetConnection.Open();
                cmd.ExecuteNonQuery();
                m_cnADONetConnection.Close();
                commited = true;
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error: {0}", e.Errors[0].Message);
            }
            return commited;

        }

        public DataTable procedure(string name)
        {

            DataTable Tb_Table = new DataTable();
            m_cnADONetConnection = new SqlConnection(dbcon);
            Ad_ms_Table = new SqlDataAdapter();
            CmBl_ms_Table = new SqlCommandBuilder(Ad_ms_Table);
            Tb_Table = new DataTable();
            Ad_ms_Table.SelectCommand = new SqlCommand(name, m_cnADONetConnection);
            Ad_ms_Table.SelectCommand.CommandType = CommandType.StoredProcedure;
            Ad_ms_Table.SelectCommand.Parameters.Clear();
            // Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@pr1", SqlDbType.Int));
            // Ad_ms_Table.SelectCommand.Parameters["@pr1"].Value = encoderid;
            m_cnADONetConnection.Open();
            Tb_Table.Clear();
            Ad_ms_Table.Fill(Tb_Table);
            m_cnADONetConnection.Close();
            return Tb_Table;
        }

        public DataTable FindStringInTable(string text,int from,int count)
        {

            DataTable Tb_Table = new DataTable();
            m_cnADONetConnection = new SqlConnection(dbcon);
            Ad_ms_Table = new SqlDataAdapter();
            CmBl_ms_Table = new SqlCommandBuilder(Ad_ms_Table);
            Tb_Table = new DataTable();
            Ad_ms_Table.SelectCommand = new SqlCommand("FindStringInTable", m_cnADONetConnection);
            Ad_ms_Table.SelectCommand.CommandType = CommandType.StoredProcedure;
            Ad_ms_Table.SelectCommand.Parameters.Clear();
            Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@stringToFind", SqlDbType.Text));
            Ad_ms_Table.SelectCommand.Parameters["@stringToFind"].Value = '%'+text+'%';
            Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@schema", SqlDbType.Text));
            Ad_ms_Table.SelectCommand.Parameters["@schema"].Value = "dbo";
            Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@table", SqlDbType.Text));
            Ad_ms_Table.SelectCommand.Parameters["@table"].Value ="Archyvas$";
          //  Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@from", SqlDbType.Text));
          //  Ad_ms_Table.SelectCommand.Parameters["@from"].Value = from.ToString();
          //  Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@count", SqlDbType.Text));
          //  Ad_ms_Table.SelectCommand.Parameters["@count"].Value = count.ToString();
            m_cnADONetConnection.Open();
            Tb_Table.Clear();
            Ad_ms_Table.Fill(Tb_Table);
            m_cnADONetConnection.Close();
            return Tb_Table;
        }

        public DataTable FindStringInTableArch(string text, int from, int count)
        {

            DataTable Tb_Table = new DataTable();
            m_cnADONetConnection = new SqlConnection(dbcon);
            Ad_ms_Table = new SqlDataAdapter();
            CmBl_ms_Table = new SqlCommandBuilder(Ad_ms_Table);
            Tb_Table = new DataTable();
            Ad_ms_Table.SelectCommand = new SqlCommand("FindStringInTable", m_cnADONetConnection);
            Ad_ms_Table.SelectCommand.CommandType = CommandType.StoredProcedure;
            Ad_ms_Table.SelectCommand.Parameters.Clear();
            Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@stringToFind", SqlDbType.Text));
            Ad_ms_Table.SelectCommand.Parameters["@stringToFind"].Value = '%' + text + '%';
            Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@schema", SqlDbType.Text));
            Ad_ms_Table.SelectCommand.Parameters["@schema"].Value = "dbo";
            Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@table", SqlDbType.Text));
            Ad_ms_Table.SelectCommand.Parameters["@table"].Value = "Archyvas$Archyve";
           // Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@from", SqlDbType.Text));
           // Ad_ms_Table.SelectCommand.Parameters["@from"].Value = from.ToString();
           // Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@count", SqlDbType.Text));
           // Ad_ms_Table.SelectCommand.Parameters["@count"].Value = count.ToString();
            m_cnADONetConnection.Open();
            Tb_Table.Clear();
            Ad_ms_Table.Fill(Tb_Table);
            m_cnADONetConnection.Close();
            return Tb_Table;
        }

        public DataTable FindStringInTable(string text, string Table)
        {

            DataTable Tb_Table = new DataTable();
            m_cnADONetConnection = new SqlConnection(dbcon);
            Ad_ms_Table = new SqlDataAdapter();
            CmBl_ms_Table = new SqlCommandBuilder(Ad_ms_Table);
            Tb_Table = new DataTable();
            Ad_ms_Table.SelectCommand = new SqlCommand("FindStringInTable", m_cnADONetConnection);
            Ad_ms_Table.SelectCommand.CommandType = CommandType.StoredProcedure;
            Ad_ms_Table.SelectCommand.Parameters.Clear();
            Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@stringToFind", SqlDbType.Text));
            Ad_ms_Table.SelectCommand.Parameters["@stringToFind"].Value = '%' + text + '%';
            Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@schema", SqlDbType.Text));
            Ad_ms_Table.SelectCommand.Parameters["@schema"].Value = "dbo";
            Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@table", SqlDbType.Text));
            Ad_ms_Table.SelectCommand.Parameters["@table"].Value = Table;
            //  Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@from", SqlDbType.Text));
            //  Ad_ms_Table.SelectCommand.Parameters["@from"].Value = from.ToString();
            //  Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@count", SqlDbType.Text));
            //  Ad_ms_Table.SelectCommand.Parameters["@count"].Value = count.ToString();
            m_cnADONetConnection.Open();
            Tb_Table.Clear();
            Ad_ms_Table.Fill(Tb_Table);
            m_cnADONetConnection.Close();
            return Tb_Table;
        }

        public DataTable FindStringInTableAdv(string text)
        {

            DataTable Tb_Table = new DataTable();
            m_cnADONetConnection = new SqlConnection(dbcon);
            Ad_ms_Table = new SqlDataAdapter();
            CmBl_ms_Table = new SqlCommandBuilder(Ad_ms_Table);
            Tb_Table = new DataTable();
            Ad_ms_Table.SelectCommand = new SqlCommand("FindStringInTable", m_cnADONetConnection);
            Ad_ms_Table.SelectCommand.CommandType = CommandType.StoredProcedure;
            Ad_ms_Table.SelectCommand.Parameters.Clear();
            Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@stringToFind", SqlDbType.Text));
            Ad_ms_Table.SelectCommand.Parameters["@stringToFind"].Value = '%' + text + '%';
            Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@schema", SqlDbType.Text));
            Ad_ms_Table.SelectCommand.Parameters["@schema"].Value = "dbo";
            Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@table", SqlDbType.Text));
            Ad_ms_Table.SelectCommand.Parameters["@table"].Value = "Advertisers";
            m_cnADONetConnection.Open();
            Tb_Table.Clear();
            Ad_ms_Table.Fill(Tb_Table);
            m_cnADONetConnection.Close();
            return Tb_Table;
        }

    }
}
